package com.imut.lagain.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.imut.lagain.entity.AccessLog;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 访问日志数据访问层
 */
@Mapper
public interface AccessLogRepository extends BaseMapper<AccessLog> {
    
    /**
     * 鏍规嵁鐢ㄦ埛ID鏌ヨ璁块棶鏃ュ織
     * @param userId 鐢ㄦ埛ID
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE user_id = #{userId} ORDER BY access_time DESC")
    IPage<AccessLog> findByUserId(@Param("userId") Long userId, Page<AccessLog> page);
    
    /**
     * 鏍规嵁鐢ㄦ埛ID鍒嗛〉鏌ヨ璁块棶鏃ュ織
     * @param userId 鐢ㄦ埛ID
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE user_id = #{userId} ORDER BY access_time DESC")
    IPage<AccessLog> findByUserIdWithPage(@Param("userId") Long userId, Page<AccessLog> page);
    
    /**
     * 鏍规嵁璁块棶璺緞鏌ヨ鏃ュ織
     * @param path 璁块棶璺緞
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE path = #{path} ORDER BY access_time DESC")
    IPage<AccessLog> findByPath(@Param("path") String path, Page<AccessLog> page);
    
    /**
     * 鏍规嵁璁块棶璺緞鍒嗛〉鏌ヨ鏃ュ織
     * @param path 璁块棶璺緞
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE path = #{path} ORDER BY access_time DESC")
    IPage<AccessLog> findByPathWithPage(@Param("path") String path, Page<AccessLog> page);
    
    /**
     * 鏍规嵁璇锋眰鏂规硶鏌ヨ鏃ュ織
     * @param method 璇锋眰鏂规硶
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE method = #{method} ORDER BY access_time DESC")
    IPage<AccessLog> findByMethod(@Param("method") String method, Page<AccessLog> page);
    
    /**
     * 鏍规嵁璇锋眰鏂规硶鍒嗛〉鏌ヨ鏃ュ織
     * @param method 璇锋眰鏂规硶
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE method = #{method} ORDER BY access_time DESC")
    IPage<AccessLog> findByMethodWithPage(@Param("method") String method, Page<AccessLog> page);
    
    /**
     * 鏍规嵁IP鍦板潃鏌ヨ鏃ュ織
     * @param ipAddress IP鍦板潃
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE ip_address = #{ipAddress} ORDER BY access_time DESC")
    IPage<AccessLog> findByIpAddress(@Param("ipAddress") String ipAddress, Page<AccessLog> page);
    
    /**
     * 鏍规嵁IP鍦板潃鍒嗛〉鏌ヨ鏃ュ織
     * @param ipAddress IP鍦板潃
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE ip_address = #{ipAddress} ORDER BY access_time DESC")
    IPage<AccessLog> findByIpAddressWithPage(@Param("ipAddress") String ipAddress, Page<AccessLog> page);
    
    /**
     * 鏍规嵁鐘舵€佺爜鏌ヨ鏃ュ織
     * @param statusCode 鐘舵€佺爜
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE status_code = #{statusCode} ORDER BY access_time DESC")
    IPage<AccessLog> findByStatusCode(@Param("statusCode") Integer statusCode, Page<AccessLog> page);
    
    /**
     * 鏍规嵁鐘舵€佺爜鍒嗛〉鏌ヨ鏃ュ織
     * @param statusCode 鐘舵€佺爜
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE status_code = #{statusCode} ORDER BY access_time DESC")
    IPage<AccessLog> findByStatusCodeWithPage(@Param("statusCode") Integer statusCode, Page<AccessLog> page);
    
    /**
     * 鏌ヨ鎸囧畾鏃堕棿鑼冨洿鍐呯殑璁块棶鏃ュ織
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime} ORDER BY access_time DESC")
    IPage<AccessLog> findByAccessTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, Page<AccessLog> page);
    
    /**
     * 鏌ヨ鐢ㄦ埛鍦ㄦ寚瀹氭椂闂磋寖鍥村唴鐨勮闂棩蹇?
     * @param userId 鐢ㄦ埛ID
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 璁块棶鏃ュ織鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE user_id = #{userId} AND access_time BETWEEN #{startTime} AND #{endTime} ORDER BY access_time DESC")
    List<AccessLog> findByUserIdAndAccessTimeBetween(@Param("userId") Long userId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 鏌ヨ鍝嶅簲鏃堕棿瓒呰繃鎸囧畾鍊肩殑鏃ュ織
     * @param responseTime 鍝嶅簲鏃堕棿闃堝€硷紙姣锛?
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE response_time > #{responseTime} ORDER BY response_time DESC")
    IPage<AccessLog> findByResponseTimeGreaterThan(@Param("responseTime") Long responseTime, Page<AccessLog> page);
    
    /**
     * 鏌ヨ鍝嶅簲鏃堕棿鍦ㄦ寚瀹氳寖鍥村唴鐨勬棩蹇?
     * @param minTime 鏈€灏忓搷搴旀椂闂?
     * @param maxTime 鏈€澶у搷搴旀椂闂?
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE response_time BETWEEN #{minTime} AND #{maxTime} ORDER BY response_time DESC")
    IPage<AccessLog> findByResponseTimeBetween(@Param("minTime") Long minTime, @Param("maxTime") Long maxTime, Page<AccessLog> page);
    
    /**
     * 鏌ヨ閿欒鐘舵€佺爜鐨勮闂棩蹇楋紙4xx, 5xx锛?
     * @param page 鍒嗛〉鍙傛暟
     * @return 璁块棶鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE status_code >= 400 ORDER BY access_time DESC")
    IPage<AccessLog> findErrorLogs(Page<AccessLog> page);
    
    /**
     * 缁熻鐢ㄦ埛璁块棶娆℃暟
     * @param userId 鐢ㄦ埛ID
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE user_id = #{userId}")
    Long countByUserId(@Param("userId") Long userId);
    
    /**
     * 缁熻鎸囧畾璺緞鐨勮闂鏁?
     * @param path 璁块棶璺緞
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE path = #{path}")
    Long countByPath(@Param("path") String path);
    
    /**
     * 缁熻鎸囧畾IP鐨勮闂鏁?
     * @param ipAddress IP鍦板潃
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE ip_address = #{ipAddress}")
    Long countByIpAddress(@Param("ipAddress") String ipAddress);
    
    /**
     * 缁熻鎸囧畾鏃堕棿鑼冨洿鍐呯殑璁块棶娆℃暟
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime}")
    Long countByAccessTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 缁熻鐢ㄦ埛鍦ㄦ寚瀹氭椂闂磋寖鍥村唴鐨勮闂鏁?
     * @param userId 鐢ㄦ埛ID
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE user_id = #{userId} AND access_time BETWEEN #{startTime} AND #{endTime}")
    Long countByUserIdAndAccessTimeBetween(@Param("userId") Long userId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 缁熻閿欒璁块棶娆℃暟
     * @return 閿欒璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE status_code >= 400")
    Long countErrorAccess();
    
    /**
     * 缁熻鎸囧畾鐘舵€佺爜鐨勮闂鏁?
     * @param statusCode 鐘舵€佺爜
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE status_code = #{statusCode}")
    Long countByStatusCode(@Param("statusCode") Integer statusCode);
    
    /**
     * 鏌ヨ鏈€杩戠殑璁块棶鏃ュ織
     * @param limit 闄愬埗鏁伴噺
     * @return 璁块棶鏃ュ織鍒楄〃
     */
    @Select("SELECT * FROM access_log ORDER BY access_time DESC LIMIT #{limit}")
    List<AccessLog> findRecentLogs(@Param("limit") Integer limit);
    
    /**
     * 鏌ヨ鐢ㄦ埛鏈€杩戠殑璁块棶鏃ュ織
     * @param userId 鐢ㄦ埛ID
     * @param limit 闄愬埗鏁伴噺
     * @return 璁块棶鏃ュ織鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE user_id = #{userId} ORDER BY access_time DESC LIMIT #{limit}")
    List<AccessLog> findRecentLogsByUserId(@Param("userId") Long userId, @Param("limit") Integer limit);
    
    /**
     * 鏌ヨ璁块棶璺緞缁熻
     * @return 璺緞璁块棶缁熻鍒楄〃锛堣矾寰勶紝娆℃暟锛?
     */
    @Select("SELECT path, COUNT(*) as count FROM access_log GROUP BY path ORDER BY count DESC")
    List<Object[]> getPathStatistics();
    
    /**
     * 鏌ヨIP璁块棶缁熻
     * @return IP璁块棶缁熻鍒楄〃锛圛P锛屾鏁帮級
     */
    @Select("SELECT ip_address, COUNT(*) as count FROM access_log GROUP BY ip_address ORDER BY count DESC")
    List<Object[]> getIpStatistics();
    
    /**
     * 鏌ヨ鐘舵€佺爜缁熻
     * @return 鐘舵€佺爜缁熻鍒楄〃锛堢姸鎬佺爜锛屾鏁帮級
     */
    @Select("SELECT status_code, COUNT(*) as count FROM access_log GROUP BY status_code ORDER BY count DESC")
    List<Object[]> getStatusCodeStatistics();
    
    /**
     * 鏌ヨ鐢ㄦ埛璁块棶缁熻
     * @return 鐢ㄦ埛璁块棶缁熻鍒楄〃锛堢敤鎴稩D锛屾鏁帮級
     */
    @Select("SELECT user_id, COUNT(*) as count FROM access_log WHERE user_id IS NOT NULL GROUP BY user_id ORDER BY count DESC")
    List<Object[]> getUserAccessStatistics();
    
    /**
     * 鏌ヨ骞冲潎鍝嶅簲鏃堕棿
     * @return 骞冲潎鍝嶅簲鏃堕棿锛堟绉掞級
     */
    @Select("SELECT AVG(response_time) FROM access_log WHERE response_time IS NOT NULL")
    Double getAverageResponseTime();
    
    /**
     * 鏌ヨ鎸囧畾璺緞鐨勫钩鍧囧搷搴旀椂闂?
     * @param path 璁块棶璺緞
     * @return 骞冲潎鍝嶅簲鏃堕棿锛堟绉掞級
     */
    @Select("SELECT AVG(response_time) FROM access_log WHERE path = #{path} AND response_time IS NOT NULL")
    Double getAverageResponseTimeByPath(@Param("path") String path);
    
    /**
     * 鏌ヨ鎸囧畾鏃堕棿鑼冨洿鍐呯殑骞冲潎鍝嶅簲鏃堕棿
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 骞冲潎鍝嶅簲鏃堕棿锛堟绉掞級
     */
    @Select("SELECT AVG(response_time) FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime} AND response_time IS NOT NULL")
    Double getAverageResponseTimeByTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 鏌ヨ鐙珛璁垮鏁帮紙鍩轰簬IP锛?
     * @return 鐙珛璁垮鏁?
     */
    @Select("SELECT COUNT(DISTINCT ip_address) FROM access_log")
    Long getUniqueVisitorCount();
    
    /**
     * 缁熻璁块棶娆℃暟
     * @param userId 鐢ㄦ埛ID
     * @param path 璺緞
     * @param method 鏂规硶
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 璁块棶娆℃暟
     */
    @Select("SELECT COUNT(*) FROM access_log WHERE (#{userId} IS NULL OR user_id = #{userId}) AND (#{path} IS NULL OR path = #{path}) AND (#{method} IS NULL OR method = #{method}) AND access_time BETWEEN #{startTime} AND #{endTime}")
    Long countAccess(@Param("userId") Long userId, @Param("path") String path, @Param("method") String method, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 缁熻鎸囧畾鏃堕棿鑼冨洿鍐呯殑鐙珛璁垮鏁?
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 鐙珛璁垮鏁?
     */
    @Select("SELECT COUNT(DISTINCT ip_address) FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime}")
    Long countUniqueVisitors(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 鏌ヨ鐑棬璺緞
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @param limit 闄愬埗鏁伴噺
     * @return 鐑棬璺緞鍒楄〃
     */
    @Select("SELECT path, COUNT(*) as count FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime} GROUP BY path ORDER BY count DESC LIMIT #{limit}")
    List<Map<String, Object>> findPopularPaths(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("limit") Integer limit);
    
    /**
     * 鏌ヨ娲昏穬鐢ㄦ埛
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @param limit 闄愬埗鏁伴噺
     * @return 娲昏穬鐢ㄦ埛鍒楄〃
     */
    @Select("SELECT user_id, COUNT(*) as count FROM access_log WHERE user_id IS NOT NULL AND access_time BETWEEN #{startTime} AND #{endTime} GROUP BY user_id ORDER BY count DESC LIMIT #{limit}")
    List<Map<String, Object>> findActiveUsers(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("limit") Integer limit);
    
    /**
     * 鍒犻櫎鎸囧畾鏃堕棿涔嬪墠鐨勮闂棩蹇?
     * @param beforeTime 鏃堕棿鐐?
     * @return 鍒犻櫎鐨勮褰曟暟
     */
    @Delete("DELETE FROM access_log WHERE access_time < #{beforeTime}")
    int deleteByAccessTimeBefore(@Param("beforeTime") LocalDateTime beforeTime);
    
    /**
     * 淇findErrorLogs鏂规硶锛屾坊鍔犳椂闂磋寖鍥村弬鏁?
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @param page 鍒嗛〉鍙傛暟
     * @return 閿欒鏃ュ織鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM access_log WHERE status_code >= 400 AND access_time BETWEEN #{startTime} AND #{endTime} ORDER BY access_time DESC")
    IPage<AccessLog> findErrorLogsByTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, Page<AccessLog> page);
    
    /**
     * 缁熻鎸囧畾鏃堕棿鑼冨洿鍐呰矾寰勭殑璁块棶娆℃暟
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 璺緞璁块棶缁熻
     */
    @Select("SELECT path, COUNT(*) as count FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime} GROUP BY path")
    List<Map<String, Object>> countByPathBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 缁熻鎸囧畾鏃堕棿鑼冨洿鍐匢P鐨勮闂鏁?
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return IP璁块棶缁熻
     */
    @Select("SELECT ip_address, COUNT(*) as count FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime} GROUP BY ip_address")
    List<Map<String, Object>> countByIpAddressBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 缁熻鎸囧畾鏃堕棿鑼冨洿鍐呯姸鎬佺爜鐨勮闂鏁?
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 鐘舵€佺爜璁块棶缁熻
     */
    @Select("SELECT status_code, COUNT(*) as count FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime} GROUP BY status_code")
    List<Map<String, Object>> countByStatusCodeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 缁熻鎸囧畾鏃堕棿鑼冨洿鍐呯敤鎴风殑璁块棶娆℃暟
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 鐢ㄦ埛璁块棶缁熻
     */
    @Select("SELECT user_id, COUNT(*) as count FROM access_log WHERE user_id IS NOT NULL AND access_time BETWEEN #{startTime} AND #{endTime} GROUP BY user_id")
    List<Map<String, Object>> countByUserIdBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 鑾峰彇鎸囧畾璺緞鍦ㄦ寚瀹氭椂闂磋寖鍥村唴鐨勫钩鍧囧搷搴旀椂闂?
     * @param path 璺緞
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 骞冲潎鍝嶅簲鏃堕棿
     */
    @Select("SELECT AVG(response_time) FROM access_log WHERE path = #{path} AND access_time BETWEEN #{startTime} AND #{endTime} AND response_time IS NOT NULL")
    Double getAverageResponseTimeByPath(@Param("path") String path, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 鏌ヨ鎸囧畾鏃堕棿鑼冨洿鍐呯殑鐙珛璁垮鏁?
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 鐙珛璁垮鏁?
     */
    @Select("SELECT COUNT(DISTINCT ip_address) FROM access_log WHERE access_time BETWEEN #{startTime} AND #{endTime}")
    Long getUniqueVisitorCountByTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
}
